<?php
defined('SYSPATH') or die ('No direct access allowed.');

/**
 *不合格实物处理页面模块,查询，修改
 * handlequality
 * @author cxy
 */
class Controller_handlequality extends Template
{
    protected $user; //登陆用户的ID
    protected $recieve_man; //登陆用户的名字
    protected $unqualified_deal_invoices=array(
                    'purchase_man'=>'计划员',
                    'purchaseorder_id'=>'VMI预约单号',
                    'check_detail_id'=>'质检明细单号',
                    'sku'=>'SKU',
                    'sku_name'=>'中文名称',
                    'supplier'=>'供应商',
                    'operate_man'=>'处理人',
                    'quality_man'=>'质检人',
                    'check_time'=>'质检时间',
                    'quality_remark'=>'质检不合格原因',
                    'status'=>'状态',
                    'quantity'=>'处理不合格数量',
                    //'fail_deal_way'=>'处理方式',
                    //'check_remark'=>'采购处理备注',
                    //'return_cost'=>'运费',
                    //'coupon_price'=>'单价',
                    'deal_time'=>'采购处理日期',
                    //'end_remark'=>'管理员处理结果'
                    );//不良品出库表头(不良品处理)
    protected $bad_product_delivery=array(
                    'id'=>'不良品出库表单号',
                    'unqualified_id'=>'不良品处理单号',
                    'purchaseorder_id'=>'VMI预约单号',
                    'sku'=>'SKU',
                    'sku_name'=>'中文名称',
                    'vProviderName'=>'供应商',
                    'quantity'=>'不合格数量',
                    'unqualified_reason'=>'质检不合格原因',
                    'quality_men'=>'质检人',
                    'purchase_man'=>'计划员',
                    'operater'=>'处理人',
                    'warehouse_remark'=>'实物出库备注',
                    'fail_deal_way'=>'采购处理方式',
                    'unqualified_reason'=>'采购处理备注',
                    'zhijian_time'=>'质检时间',
                    'deal_time'=>'处理时间',
                    'status'=>'状态',
                );//不合格处理单表头(不良品实物确认处理)

    public function before()
    {
        $userid            = User::active_user()->id;
        $this->user        = $userid;
        $username          = ORM::factory('user')
            ->where('id','=',$userid)
            ->find();
        $this->recieve_man = $username->name;
        try{
            ACL::Required('handlequality shi');
        }catch(HTTP_Exception_403 $e){
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect(URL::base(TRUE));
        }
        include_once(MODPATH.'/commonapi/classes/purchaseApi.php');
        parent::before();
    }

    //一开始就显示全部未处理的产品
    public function action_index()
    {
        $this->title = '不良品实物确认处理';
        $view        = View::factory('unqualified/quality');
        $way         = DB::select('number','value')
            ->from('statusdics')
            ->where('table','=','unqualified_deal_invoices')
            ->where('colum_name','=','fail_deal_way')
            ->execute('newerp')
            ->as_array();
        $view->way = $way;
        $this->response->body($view);
    }

    //修改和处理不合格产品的动作
    public function action_handlequality_check()
    {
        $this->title = '不良品实物确认处理';
        $instance=Database::instance('newerp');
        $instance->begin();
        //		echo "<pre>";
        //	print_r($_POST);array(DB::expr('SUM(recieve_details.arrival_quantity)'),'total_arrival_quantuty')
        //		echo "</pre>";
        $warehouse_id = Kohana::$config->get('warehouse')->warehouse_id;
        if($_POST['id']){
            $db = DB::update('bad_product_delivery')
                ->set(array('warehouse_remark' => $_POST['warehouse_remark']))
                ->set(array('warehouse_man' => $this->user))
                ->set(array('status' => 20))
                ->set(array('out_time' => date("Y-m-d H:i:s")))
                ->where('id','=',$_POST['id'])
                ->execute('newerp');
            if($db==1){

                if(false && $_POST['fail_deal_way']==5){
                    //让步接收的时候需要入良品库，然后减去采购明细单中的不合格量及质检单和收货单的质检量及不合格量
                    $info = self::insert_warehouseorders($_POST['id']);
                    if($info=="ok"||$info=="手动插入入库记录成功"){
                        echo '操作成功';
                    }
                }else{
                    if($_POST['fail_deal_way']==3){
                        $db_bad                   = DB::select()
                            ->from('bad_product_delivery')
                            ->where('id','=',$_POST['id'])
                            ->execute('newerp')
                            ->current();
                        $waster                   = ORM::factory('waster_receipt');
                        //$waster->id               = rand(-999999999,-1);
                        $waster->sku              = $db_bad['sku'];
                        $waster->sku_name         = $db_bad['sku_name'];
                        $waster->sku_standard     = $db_bad['sku_standard'];
                        $waster->storage_position = $db_bad['storage_position'];
                        $waster->quantity         = $db_bad['quantity'];

                        if($db_bad['purchaseorder_id']!=0){
                            //通过VMI预约单号获取采购单价及计税方式
                            $pod = ORM::factory('purchaseorder_detail')
                                ->where('purchaseorder_id','=',$db_bad['purchaseorder_id'])
                                ->where('sku','=',$db_bad['sku'])
                                ->find();

                            $sku_price = new Service_Common();
                            $price     = $sku_price->get_sku_money_cost($pod->single_price,$db_bad['quantity'],$pod->tax_way,$pod->tax_rate);

                            $waster->single_price = $price['single_price'];
                            $waster->money        = $price['money'];
                            $waster->cost         = $price['cost']+(($pod->transportation_expense/$pod->quantity)*$db_bad['quantity']);
                        }
                        $waster->table                = 'bad_product_delivery';
                        $waster->table_id             = $db_bad['id'];
                        $waster->remark               = '不良品报废处理，报废出库单号是'.$db_bad['id'];
                        $waster->waster_receipts_time = date('Y-m-d H:i:s');
                        $waster->save();
                        /*
                        if($waster->saved()){
                            $wasteraid = $waster->pk();
                            $wasterid  = (int)((string)$wasteraid.sprintf("%03d",$warehouse_id));
                            $sql       = "UPDATE `waster_receipts` SET `id` =".$wasterid." WHERE `aid` =".$wasteraid;
                            $dat       = DB::query(Database::UPDATE,$sql);
                            $row       = $dat->execute('newerp');
                            echo '操作成功';
                        }
                        
                        */
                       $msgstr = '操作成功';

                    }elseif ($_POST['fail_deal_way'] == 2) {
                        $res = PurchaseApi::supplierReturnsPullBad($_POST['unqualified_id']);
                        if($res['code'] == '200'){
                            $msgstr = '操作成功';
                        }else{
                            $instance->rollback();
                            echo $res['msg'];
                            exit;
                        }
                    }else{
                        $msgstr ='操作成功';
                    }
                }
            }
            //得到已经出库的不良品数量
            $select            = DB::select(array(
                DB::expr('sum(quantity)'),
                'count'
            ))
                ->from('bad_product_delivery')
                ->where('unqualified_id','=',$_POST['unqualified_id'])
                ->where('status','=',20)
                ->execute('newerp')
                ->current();
            $count             = $select['count'];
            $unqualified_total = DB::select('quantity')
                ->from('unqualified_deal_invoices')
                ->where('id','=',$_POST['unqualified_id'])
                ->execute('newerp')
                ->current();
            $unqualified_total = $unqualified_total['quantity'];
            //已经出库的不良品数量是否等于不良品处理表中的不合格数量,不良品入库量=出入库者将不良品入库单关闭
            if($count==$unqualified_total){
                $result = ORM::factory('unqualifieddeal',$_POST['unqualified_id']) //->values($_POST)
                    ->set('status',30)
                    ->set('end_man',$this->user)
                    ->set('end_time',date("Y-m-d H:i:s"))//->set('end_remark', 30)
                    ->save();
            }
        }
        echo $msgstr?$msgstr:'操作成功';
        $instance->commit();
    }


    //根据质检，供应商，日期，处理方式等的字段来查询
    public function action_handlequality_select()
    {
        $this->title = '不良品实物确认处理';
        $basic       = new Basic();
        if(!empty($_GET)){
            $way = DB::select('number','value')
                ->from('statusdics')
                ->where('table','=','unqualified_deal_invoices')
                ->where('colum_name','=','fail_deal_way')
                ->execute('newerp')
                ->as_array();

            $status     = trim($_GET['status']);
            $condition  = $_GET;
            $orms       = DB::select()
                ->from('bad_product_delivery')
                ->order_by('deal_time','DESC');
            $result_all = unqualitydb::get_info($condition,$status,$orms);
            $result_all = $orms->execute('newerp')
                ->as_array();

            $count      = count($result_all);
            $pagination = Pagination::factory(array(
                'total_items'    => $count,
                //总记录数
                'items_per_page' => 20,
                //每页显示多少条
                'uri'            => $this->request->uri(),
            ));
            $orms->limit($pagination->items_per_page);
            $orms->offset($pagination->_offset);
            $result = $orms->execute('newerp')
                ->as_array();
            if($count==0){
                Message::error('你想查找的内容没有记录');
            }
            $view = View::factory('unqualified/quality');
            $check_detail_ids=array();//check_quality
            foreach($result as $ks => $v){
                $check_detail_ids[$result[$ks]['id']]=$result[$ks]['check_detail_id'];//check_quality
                $result[$ks]['vProviderName'] = $basic->getnameByid($v['supplier']);
                if($v['operate_man']>0){
                    $result[$ks]['operater'] = Erpuser::nick_by_id($v['operate_man']);
                }else{
                    $result[$ks]['operater'] = '';
                }
                if($v['purchase_man']>0){
                    $result[$ks]['purchaser'] = Erpuser::nick_by_id($v['purchase_man']);
                }else{
                    $result[$ks]['purchaser'] = '';
                }

                if(isset($v['check_detail_id']) and $v['check_detail_id']>0){
                    $cc = ORM::factory('check_quality_detail',$v['check_detail_id']);
                    if($cc->id==''){
                        $result[$ks]['cc_remark'] = '';
                    }else{
                        $result[$ks]['cc_remark'] = $cc->remark;
                    }
                }elseif(isset($v['unqualified_id']) and $v['unqualified_id']>0){
                    $cc = ORM::factory('unqualified',$v['unqualified_id']);
                    if($cc->id==''){
                        $result[$ks]['cc_remark'] = '';
                    }else{
                        $result[$ks]['cc_remark'] = $cc->quality_remark;
                    }
                }

                //质检时间添加20150411gqc
                $result[$ks]['zhijian_time'] = '';
                if(isset($v['purchaseorder_id'])&&$v['purchaseorder_id']&&isset($v['sku'])){
                    $result[$ks]['zhijian_time'] = DB::select('warehouse_date')
                        ->from('warehouseorders')
                        ->where('purchaseorders_id','=',$v['purchaseorder_id'])
                        ->and_where('sku','=',$v['sku'])
                        ->and_where('warehouse_date','<',$v['deal_time'])
                        ->order_by('id','DESC')
                        ->limit(1)
                        ->execute('newerp')
                        ->get('warehouse_date','');
                }
            }
            //var_dump($check_detail_ids);exit;//check_quality
            !empty($check_detail_ids)&&$check_men=DB::select('id','check_man')->from('check_quality_details')->where('id','in',$check_detail_ids)->execute('newerp')->as_array('id','check_man');
            //var_dump($check_men);exit;
            !empty($check_men)&&$check_men_nick=DB::select('id','nick')->from('users')->where('id','in',$check_men)->execute('default')->as_array('id','nick');
            //var_dump($check_men_nick);exit;
            $quality_men=array();
            foreach($result as $key=>$value){
                if(isset($check_men[$result[$key]['check_detail_id']])&&
                    isset($check_men_nick[$check_men[$result[$key]['check_detail_id']]])){
                    $quality_men[$result[$key]['id']]=$check_men_nick[$check_men[$result[$key]['check_detail_id']]];
                }else{
                    $quality_men[$result[$key]['id']]='';
                }
            }
            $page_links       = $pagination->render(); //渲染分页到模板
            $view->page_links = $page_links;
            $view->orm        = $result;
            $view->way        = $way;
            $view->quality_men=$quality_men;

            $view->purchase_man_name = isset($_GET['purchase_man_name'])?$_GET['purchase_man_name']:NULL;
            $view->supplier          = isset($_GET['supplier'])?$_GET['supplier']:NULL;
            $view->purchaseorder_id  = isset($_GET['purchaseorder_id'])?$_GET['purchaseorder_id']:NULL;
            $view->warehouse_man     = isset($_GET['warehouse_man'])?$_GET['warehouse_man']:NULL;
            $view->sku               = isset($_GET['sku'])?$_GET['sku']:NULL;
            $view->date_from_c       = isset($_GET['date_from_c'])?$_GET['date_from_c']:NULL;
            $view->date_to_c         = isset($_GET['date_to_c'])?$_GET['date_to_c']:NULL;
            $view->date_from         = isset($_GET['date_from'])?$_GET['date_from']:NULL;
            $view->date_to           = isset($_GET['date_to'])?$_GET['date_to']:NULL;
            $view->fail_deal_way     = isset($_GET['fail_deal_way'])?$_GET['fail_deal_way']:NULL;
            $view->no_purchaseorder_id = isset($_GET['no_purchaseorder_id']) ? $_GET['no_purchaseorder_id']: 0;
            $view->status            = isset($_GET['status'])?$_GET['status']:NULL;
            //$view->check_man=$this->recieve_man;
            $this->response->body($view);
        }else{
            $this->request->redirect("unqualified/index");
        }
    }

    //不良品出库单的让步接收入良品库
    public function insert_warehouseorders($id)
    {

        $db                = DB::select()
            ->from('bad_product_delivery')
            ->where('id','=',$id)
            ->execute('newerp')
            ->current();
        $db['supplier_id'] = $db['supplier'];

        $warehouseorders                 = ORM::factory('warehouseorder');
        //$warehouseorders->id             = rand(-999999999,-1);
        $warehouseorders->warehouse_date = date('Y-m-d H:i:s');
        $warehouseorders->op_time        = date('Y-m-d H:i:s');

        $warehouseorders->purchaseorders_id           = $db['purchaseorder_id'];
        $sql = 'SELECT warehouseid FROM purchaseorders WHERE id='.$db['purchaseorder_id'];
        $warehouse_id = DB::query(Database::SELECT,$sql)->execute('newerp')->get('warehouseid');
        $warehouseorders->unqualified_deal_invoice_id = $db['check_detail_id'];
        $warehouseorders->supplier_id                 = $db['supplier'];

        $warehouseorders->sku          = $db['sku'];
        $warehouseorders->sku_name     = $db['sku_name'];
        $warehouseorders->sku_standard = $db['sku_standard'];

        $check_quality_details             = ORM::factory('check_quality_detail',$db['check_detail_id']);
        $warehouseorders->storage_position = $check_quality_details->storage_position;

        $warehouseorders->warehouse_quantity = $db['quantity'];
        $warehouseorders->single_price       = $db['coupon_price'];
        $warehouseorders->money              = $db['quantity']*$db['coupon_price'];
        $warehouseorders->tax_price          = 0;

        $warehouseorders->transportation_expense = $db['return_cost'];
        $warehouseorders->cost                   = $warehouseorders->money+$db['return_cost'];
        $warehouseorders->type                   = 10;
        $warehouseorders->warehouse_man          = User::active_user()->id;
        $warehouseorders->store                  = $db['store'];
        $warehouseorders->warehouseid            = $warehouse_id;
        if($db['purchaseorder_id']!=0){
            $warehouseorders->remark = '采购折让入库，'.$db['warehouse_remark'];
            $warehouseorders->save();
            if($warehouseorders->saved()){
                /*
                $whaid = $warehouseorders->pk();
                $whid  = (int)((string)$whaid.sprintf("%03d",$warehouse_id));
                $sql   = "UPDATE `warehouseorders` SET `id` =".$whid." WHERE `aid` =".$whaid;
                $dat   = DB::query(Database::UPDATE,$sql);
                $row   = $dat->execute('newerp');
                */

                $num         = qualitycomm::selcet_purchaseorder_details_quantity($db['sku'],$db['purchaseorder_id']); //查询采购明细表中的不合格量
                $count_total = $num['num']-$db['quantity']; //总的质检不合格量减去让步接收的量
                $notice3     = qualitycomm::update_details_quantity($db['check_detail_id'],$db['quantity']); //回写质检明细表，修改合格量和不合格量
                $res         = qualitycomm::update_purchaseorder_details_quantity($db['sku'],$db['purchaseorder_id'],$count_total); //更新采购明细的不合格量
                //$notice      = qcwo::inventory_notice($db); //入库时，判断库存表是否有期初值，没有则加
                $dbarray = array();
                array_push($dbarray,$db);
                $notice  = qcwo::inventory_notice($dbarray); //入库时，判断库存表是否有期初值，没有则加
                $arr_wo  = qcwo::select_po_wo($db['purchaseorder_id'],$db['sku'],$db['quantity']); //获取这个采购单的这个sku总的入库量
                $notice2 = purchase::update_po($arr_wo); //采购单通过入库量回写状态
                if($notice=='ok'&&$notice2=='ok'&&$notice3=='ok'){
                    $info = 'ok';
                }else{
                    $info = '入库记录插入库存成本表失败或采购单回写状态或回写新的质检单失败';
                }
            }else{
                $info = '系统中插入入库记录失败';
            }
        }else{
            //echo '没有采购单';
            $warehouseorders->remark = $db['warehouse_remark'];

            $warehouseorders->save();

            if($warehouseorders->saved()){
                $warehouseorders_aid = $warehouseorders->pk();
                /*
                $riid                = (int)((string)$warehouse_id.sprintf("%03d",$warehouse_id));
                $sql                 = "UPDATE `warehouseorders` SET `id` =".$riid." WHERE `aid` =".$warehouseorders_aid;
                $dat                 = DB::query(Database::UPDATE,$sql);
                $row                 = $dat->execute('newerp');
                */
                $info = '手动插入入库记录成功';
            }else{
                $info = '手动插入入库记录失败';
            }

        }

        return $info;
    }

    //下载
    public function action_download()
    {
        set_time_limit(0);
        ini_set('memory_limit','512M');
        require_once(Kohana::find_file('vendor','PHPExcel/PHPExcel/IOFactory'));

        $basic = new Basic();
        if(!empty($_GET)){
            $way = DB::select('number','value')
                ->from('statusdics')
                ->where('table','=','unqualified_deal_invoices')
                ->where('colum_name','=','fail_deal_way')
                ->execute('newerp')
                ->as_array('number','value');
            $warehouse = DB::select('store_id', 'store_name')
                ->from('branch_stores')
                ->execute('newerp')
                ->as_array('store_id', 'store_name');

            $status     = trim($_GET['status']);
            $condition  = $_GET;
            $orms       = DB::select()
                ->from($_GET['tableName']);
            $result_all = unqualitydb::get_info($condition,$status,$orms);

            foreach ($result_all as $key => $value) {
                    $check_detail_ids[] = $value['check_detail_id'];
            }
            $check_detail_ids = implode(',',$check_detail_ids);
            $sql = "SELECT id,check_man,invoice_date,remark FROM check_quality_details WHERE id in($check_detail_ids)";
            $check_detail = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('id');
            
            if($_GET['tableName'] == 'unqualified_deal_invoices'){
                $columnNames = $this->unqualified_deal_invoices;
                $fileName = date('Y-m-d').'采购员不良品处理.xls';
                foreach($result_all as $ks => &$v){
                    $v['supplier']=$basic->getnameByid($v['supplier']);
                    $v['operate_man'] = Erpuser::nick_by_id($v['operate_man']);
                    $v['quality_man'] = Erpuser::nick_by_id($v['quality_man']);
                    $v['check_remark'] = $v['unqualified_reason'];
                    $v['status'] = $v['status']==20?'已处理':'未处理';
                    $v['store'] = $warehouse[$v['store']];
                    $v['purchase_man'] = Erpuser::nick_by_id($v['purchase_man']);
                }
            }else{
                $columnNames = $this->bad_product_delivery;
                $fileName = date('Y-m-d').'不良品仓库实物处理.xls';
                foreach($result_all as $ks => &$v){
                    $v['vProviderName'] = $basic->getnameByid($v['supplier']);
                    $v['status'] = $v['status']==20?'已出库':'待出库';
                    $v['operater'] = Erpuser::nick_by_id($v['operate_man']);
                    $v['purchase_man'] = Erpuser::nick_by_id($v['purchase_man']);
                    $v['zhijian_time'] = $check_detail[$v['check_detail_id']]['invoice_date'];
                    $v['quality_men'] = Erpuser::nick_by_id($check_detail[$v['check_detail_id']]['check_man']);

                    $v['fail_deal_way'] = $way[$v['fail_deal_way']];

                }
            }
            self::createXLSTable($columnNames,$result_all,$fileName);

        }  
    }

    /**
     *根据列明和已获得的相关信息数组生成table类型的字符串输出
     */
    static protected function createXLSTable($columnNames,$datas,$fileName){
        $keys = array_keys($columnNames);
        $content = "";
        $content .= "<table border='1'><tr>";
        foreach($columnNames as $key => $columnName){
            $content .= "<td>$columnName</td>";
        }
        $content .= "</tr>";
        foreach($datas as $key => $data){
            $content .= "<tr>";
            foreach($keys as $key){
                $content .= "<td>".$data[$key]."</td>";
            }
            $content .= "</tr>";
        }
        $content .= "</table>";
        header("Content-type:application/vnd.ms-execl;charset=gb2312");
        header("Content-Disposition:attactment;filename=$fileName");
        header("Pragma: no-cache");
        header("Expires: 0");
        echo $content;
        exit;
    } 
}
